package AccountWindow.Customer.RoomDetailWindow.Dao;

import AccountWindow.Customer.RoomDetailWindow.Room;
import SignAndRegister.RemindWindow;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;

public class RoomDetailDao {
    String url = "jdbc:mysql://localhost:3306/hims";
    String user = "root";
    String password = "511300";

    public int add(Room addRoom) throws Exception{
        Class.forName("com.mysql.cj.jdbc.Driver"); //注册驱动
        Connection con = DriverManager.getConnection(url, user, password);  //获取连接
        Statement stat = con.createStatement(); //获取执行者对象
        int row = 0;
        String sql = "INSERT INTO room VALUES ('"+addRoom.roomID+"','"+addRoom.roomType+"','"+addRoom.roomCode+"','"+addRoom.roomStatus+"')";
        row += stat.executeUpdate(sql);

        stat.close();
        con.close();

        return row;
    }

    public int remove(Room deleteRoom) throws Exception{
        Class.forName("com.mysql.cj.jdbc.Driver"); //注册驱动
        Connection con = DriverManager.getConnection(url, user, password);  //获取连接
        Statement stat = con.createStatement(); //获取执行者对象
        int row = 0;

        String sql = "DELETE FROM room WHERE roomID = '"+deleteRoom.roomID+"'";
        row += stat.executeUpdate(sql);

        stat.close();
        con.close();

        return row;
    }

    public Vector<Vector> queryByTypeAndStatus(Room selectedRoom) throws Exception{
        Vector<Vector> data = new Vector<>();

        Class.forName("com.mysql.cj.jdbc.Driver"); //注册驱动
        Connection con = DriverManager.getConnection(url, user, password);  //获取连接
        Statement stat = con.createStatement(); //获取执行者对象
        String sql;
        boolean isAllType = selectedRoom.roomType.toString().equals("任何类型");
        boolean isAllStatus = selectedRoom.roomStatus.toString().equals("任何状态");
        if(isAllType && isAllStatus){
            System.out.println("1");
            sql = "SELECT * FROM room";
        } else if (isAllType && !isAllStatus) {
            System.out.println("2");
            sql = "SELECT * FROM room WHERE roomStatus = '"+selectedRoom.roomStatus+"'";
        } else if (!isAllType && isAllStatus) {
            System.out.println("3");
            sql = "SELECT * FROM room WHERE roomType = '"+selectedRoom.roomType+"'";
        } else {
            System.out.println("4");
            sql = "SELECT * FROM room WHERE roomType = '"+selectedRoom.roomType+"'AND roomStatus = '"+selectedRoom.roomStatus+"'";
        }

        ResultSet rs =stat.executeQuery(sql);

        while(rs.next()) {

            String roomID = rs.getString("roomID");
            String roomCode = "******";
            String roomType = rs.getString("roomType");
            String roomStatus = rs.getString("roomStatus");

            Vector line = new Vector();
            line.add(roomID);
            line.add(roomCode);
            line.add(roomType);
            line.add(roomStatus);

            data.add(line);
        }
        return data;
    }

    public Room queryByID(String ID) throws Exception{

        Room queryByIDRoom;

        if(ID==null){
            return null;
        }else{
            Class.forName("com.mysql.cj.jdbc.Driver"); //注册驱动
            Connection con = DriverManager.getConnection(url, user, password);  //获取连接
            Statement stat = con.createStatement(); //获取执行者对象
            String sql = "SELECT * FROM room WHERE roomID = '"+ID+"'";
            ResultSet rs =stat.executeQuery(sql);
            while (rs.next()){
                String roomID = rs.getString("roomID");
                String roomCode = "******";
                String roomType = rs.getString("roomType");
                String roomStatus = rs.getString("roomStatus");

                queryByIDRoom = new Room(roomID,roomCode,roomType,roomStatus);

                return queryByIDRoom;
            }
        }

        return null;
    }

    public Vector<Vector> query() throws Exception{

        Vector<Vector> data = new Vector<>();

        Class.forName("com.mysql.cj.jdbc.Driver"); //注册驱动
        Connection con = DriverManager.getConnection(url, user, password);  //获取连接
        Statement stat = con.createStatement(); //获取执行者对象
        String sql = "SELECT * FROM room";
        ResultSet rs =stat.executeQuery(sql);

        while(rs.next()){

            String roomID = rs.getString("roomID");
            String roomCode = "******";
            String roomType = rs.getString("roomType");
            String roomStatus = rs.getString("roomStatus");

            Vector line = new Vector();
            line.add(roomID);
            line.add(roomCode);
            line.add(roomType);
            line.add(roomStatus);

            data.add(line);
        }

        rs.close();
        stat.close();
        con.close();

        return data;
    }

    public int modify(Room modifyRoom) throws  Exception{
        Class.forName("com.mysql.cj.jdbc.Driver"); //注册驱动
        Connection con = DriverManager.getConnection(url, user, password);  //获取连接
        Statement stat = con.createStatement(); //获取执行者对象
        int row = 0;

        String targetRoomID = null;
        String sql ="SELECT * FROM room WHERE roomID = '"+modifyRoom.roomID+"'";
        ResultSet rs = stat.executeQuery(sql);
        while(rs.next()){
            targetRoomID = rs.getString("roomID");
        }
        if(targetRoomID==null){
            new RemindWindow("NoThisRoom");
            row+=1;
        }else{
            sql = "UPDATE room SET roomID='"+modifyRoom.roomID+"',roomCode='"+modifyRoom.roomCode+"',roomStatus='"+modifyRoom.roomStatus+"',roomType='"+modifyRoom.roomType+"' WHERE roomID ='"+modifyRoom.roomID+"'";
            row += stat.executeUpdate(sql);
        }
        return row;
    }
}
